package com.conference.repository;

import com.conference.entity.Conference;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Repository
public interface ConferenceRepository extends JpaRepository<Conference, Long>, JpaSpecificationExecutor<Conference> {
    
    @Query("SELECT COUNT(c) FROM Conference c WHERE c.status = ?1")
    long countByStatus(Integer status);
    
    @Query("SELECT COUNT(c) FROM Conference c WHERE c.status = ?1 AND c.startTime > ?2")
    long countByStatusAndStartTimeAfter(Integer status, LocalDateTime dateTime);
    
    @Query("SELECT c FROM Conference c WHERE c.startTime BETWEEN ?1 AND ?2")
    List<Conference> findByStartTimeBetween(LocalDateTime startDate, LocalDateTime endDate);
    
    @Query(value = "SELECT v.venue_name as venueName, COUNT(DISTINCT c.id) as usageCount " +
           "FROM conference_venue v " +
           "LEFT JOIN conference c ON v.conference_id = c.id " +
           "GROUP BY v.venue_name " +
           "ORDER BY usageCount DESC " +
           "LIMIT 10", nativeQuery = true)
    List<Map<String, Object>> findVenueUsageStatistics();
    
    @Query(value = "SELECT MONTH(c.start_time) as month, COUNT(*) as count " +
           "FROM conference c " +
           "WHERE c.start_time BETWEEN ?1 AND ?2 " +
           "GROUP BY MONTH(c.start_time) " +
           "ORDER BY month", nativeQuery = true)
    List<Map<String, Object>> findMonthlyConferenceCount(LocalDateTime startDate, LocalDateTime endDate);
    
    Page<Conference> findAll(Pageable pageable);
} 